Skip to main content

Snowflake actions

1. Client Config​

Usage​

The Snowflake Client Config Connector allows you to configure and establish connections to the Snowflake data warehousing platform within the IgniteConnex ecosystem. This connector is essential for managing and accessing Snowflake data using IgniteConnex.

Prerequisites​

Before using the connector, ensure you have:

  • Created a Snowflake account and acquired the necessary credentials (account, username, password).
  • Familiarity with Snowflake's configuration parameters such as warehouse.

Connector Configuration​

To set up a Snowflake Client Config using the connector, provide the following configuration parameters:

  • Context: A unique identifier for the Snowflake connection context.

  • Account: The Snowflake account in the format organization-account-identifier.

  • Username: The username associated with the Snowflake account.

  • Password: The password for the Snowflake account.

  • Warehouse: The Snowflake warehouse to use for queries.

Overriding Properties​

The properties of the connector can be overridden by the following message properties, if provided:

  • Context: msg.config.context
  • Username: msg.config.username
  • Account: msg.config.account
  • Password: msg.config.password
  • Warehouse: msg.config.warehouse

Connector Functionality​

The connector performs the following actions:

  1. Creates a Snowflake connection using the provided configuration parameters.
  2. Connects to the Snowflake account using the configured credentials.
  3. Stores the established connection and configuration details for future use.

Sample msg.config Model:​

msg.config = {
    "context": "<YourConnectionContext>",
    "account": "<your_account_url>",
    "username": "<your_username>",
    "password": "<your_password>",
    "warehouse": "<your_warehouse>"
}

2. Execute Query​

Usage​

The provided connector enables you to execute SQL queries against Snowflake using the IgniteConnex platform. This script supports two modes of query execution: bulk and stream.

Prerequisites​

Before using the script, ensure you have:

  • A Snowflake connection established in the IgniteConnex platform.

Connector Configuration​

To execute a query using the Snowflake Execute Query Connector, you will need the following:

  • Context: This is a unique identifier assigned to your Snowflake connection. Specify the Context for which you want to run the query.

  • Query: Enter the SQL query you wish to execute. Ensure the type is a string.

  • Binds_Path: Binds_Path contains the path to your binds. By default, the path is msg.data, and it should contain the necessary binds.

  • Output: The connector provides different outputs based on the chosen stream consumption mode:

    • bulk: An array of rows is sent as a payload.
    • stream: Rows are streamed one by one, with stream status updates sent as node status.

Overriding Properties​

You can override the properties of the connector by providing the following message properties:

  • Context: msg.config.context
  • Query: msg.config.query
  • Binds_Path: msg.config.binds_path

Sample msg.config Model:​

msg.config = {
    "Context": "<YourContextName>",
    "Query": "SELECT * FROM database.schema.table",
    "Binds_Path": "msg.data",
    "Output": "bulk"
}

3. Bulk Insert​

Usage​

The Snowflake: Bulk Insert Connector facilitates the efficient bulk insertion of data into Snowflake tables using IgniteConnex. This connector supports both synchronous and streaming output modes to suit your data integration needs.

Prerequisites​

Before using the connector, ensure you have:

  • An established Snowflake connection through the Snowflake Client Config Connector.
  • Familiarity with Snowflake table structure and data binding.

Connector Configuration​

To use the Snowflake: Bulk Insert Connector, provide the following configuration inputs:

  • Context: A unique identifier for the Snowflake connection context.

  • Database: The Snowflake database where the target table resides.

  • Schema: The schema within the Snowflake database.

  • Table: The target table for bulk data insertion.

  • Binds_Path: The path to the data for binding to placeholders in the query.

  • Binds_Mapping: A mapping of placeholders to corresponding data properties.

  • Output: The connector provides different outputs based on the chosen stream consumption mode:

    • bulk: An array of rows is sent as a payload.
    • stream: Rows are streamed one by one, with stream status updates sent as node status.

Overriding Properties​

The properties of the connector can be overridden by the following message properties, if provided:

  • Context: msg.config.context
  • Database: msg.config.database
  • Schema: msg.config.schema
  • Table: msg.config.table
  • Binds_path: msg.config.binds_path
  • Binds_mapping: msg.config.binds_mapping
  • Output: msg.config.output

Connector Functionality Functionality​

The connector performs the following actions:

  1. Establishes a connection to Snowflake using the specified context.
  2. Executes a bulk insert query with data bindings and placeholders.
  3. Provides the option to retrieve query results in bulk or stream output modes.

Sample msg.data Model :​

msg.data =[{ "user_name": "User1", "country": "USA", "age": 25 },
    { "user_name": "User2", "country": "Canada", "age": 30 },
    { "user_name": "User3", "country": "UK", "age": 22 },
    { "user_name": "User4", "country": "Australia", "age": 35 },
    { "user_name": "User5", "country": "Germany", "age": 28 }]

Sample msg.config Model:​

msg.config = {
    "context": "<YourConnectionContext>",
    "database": "<your_database>",
    "schema": "<your_schema>",
    "table": "<your_table>",
    "binds_path": "<msg.data>",
    "binds_mapping": {
        "<placeholder>": "<data_property>"
    },
    "output": "bulk" or "stream"
}